const { DataTypes } = require('sequelize');
const { sequelize } = require('../config/database');

const Chapter = sequelize.define('Chapter', {
  id: {
    type: DataTypes.STRING(20),
    primaryKey: true,
    allowNull: false,
    comment: '章节ID，如chap01, chap02等'
  },
  title: {
    type: DataTypes.STRING(100),
    allowNull: false,
    comment: '章节标题，如第1次课'
  },
  sort_order: {
    type: DataTypes.INTEGER,
    allowNull: false,
    defaultValue: 0,
    comment: '排序顺序'
  },
  status: {
    type: DataTypes.TINYINT(1),
    allowNull: false,
    defaultValue: 1,
    comment: '状态：1-启用，0-禁用'
  }
}, {
  tableName: 'chapters',
  timestamps: true,
  createdAt: 'created_at',
  updatedAt: 'updated_at',
  charset: 'utf8mb4',
  collate: 'utf8mb4_unicode_ci',
  comment: '章节表'
});

// 定义关联关系
Chapter.associate = function(models) {
  Chapter.hasMany(models.Lecture, {
    foreignKey: 'chapter_id',
    as: 'lectures',
    onDelete: 'CASCADE',
    onUpdate: 'CASCADE'
  });
};

// 静态方法
Chapter.getAllWithLectures = async function() {
  return await Chapter.findAll({
    where: { status: 1 },
    include: [{
      association: 'lectures',
      where: { status: 1 },
      required: false
    }],
    order: [['sort_order', 'ASC'], ['lectures', 'sort_order', 'ASC']]
  });
};

Chapter.getByIdWithLectures = async function(id) {
  return await Chapter.findOne({
    where: { id, status: 1 },
    include: [{
      association: 'lectures',
      where: { status: 1 },
      required: false
    }],
    order: [['lectures', 'sort_order', 'ASC']]
  });
};

Chapter.createChapter = async function(data) {
  return await Chapter.create(data);
};

Chapter.updateChapter = async function(id, data) {
  const [updatedRowsCount] = await Chapter.update(data, {
    where: { id }
  });
  return updatedRowsCount;
};

Chapter.deleteChapter = async function(id) {
  const [updatedRowsCount] = await Chapter.update(
    { status: 0 },
    { where: { id } }
  );
  return updatedRowsCount;
};

Chapter.getStats = async function() {
  const result = await Chapter.findOne({
    attributes: [
      [sequelize.fn('COUNT', sequelize.col('*')), 'total_chapters'],
      [sequelize.fn('COUNT', sequelize.literal('CASE WHEN status = 1 THEN 1 END')), 'active_chapters']
    ],
    raw: true
  });
  return result;
};

module.exports = Chapter;